package fr.umlv.m2.recipes.ideas.dao;

import android.content.ContentValues;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import fr.umlv.m2.recipes.ideas.bean.RecipeToIngredientBean;
import fr.umlv.m2.recipes.ideas.dao.interfaces.RecipesRelativeDAOInterface;
import fr.umlv.m2.recipes.ideas.table.enumerations.TableName;

/**
 * DAO uses to handle a liaison between a recipe and an ingredient
 * @author Christophe Chillan et Younes Chandarli
 *
 */
public class RecipeToIngredientDAO implements	RecipesRelativeDAOInterface<RecipeToIngredientBean> {
	
	private static final String TABLE_RECIPESTOINGREDIENT = TableName.RECIPESTOINGREDIENT.toString();
	private static final String COL_ID_RECIPES = "RECIPES_ID";
	private static final String COL_ID_INGREDIENT = "INGREDIENT_ID";
	private static final String FOREIGN_RECIPES_ID_REFERENCES="ID";
	private static final String FOREIGN_INGREDIENT_ID_REFERENCES="ID";
	private static final int NUM_COL_COUNT = 0;
	
	private final StringBuilder CREATE_DB;
	
	/**
	 * Create a recipe to ingredient DAO
	 */
	public RecipeToIngredientDAO(){
		CREATE_DB = new StringBuilder();
		CREATE_DB.append("CREATE TABLE ");
		CREATE_DB.append(TABLE_RECIPESTOINGREDIENT).append(" ");
		CREATE_DB.append("(");
		CREATE_DB.append(COL_ID_RECIPES).append(" INTEGER, ");
		CREATE_DB.append(COL_ID_INGREDIENT).append(" INTEGER, ");
		CREATE_DB.append("PRIMARY KEY(").append(COL_ID_RECIPES).append(" , ");
		CREATE_DB.append(COL_ID_INGREDIENT).append(") , ");
		CREATE_DB.append("FOREIGN KEY(").append(COL_ID_RECIPES).append(") ");
		CREATE_DB.append(" REFERENCES ").append(TableName.RECIPES.toString()).append("(");
		CREATE_DB.append(FOREIGN_RECIPES_ID_REFERENCES).append("), ");
		CREATE_DB.append("FOREIGN KEY(").append(COL_ID_INGREDIENT).append(") ");
		CREATE_DB.append(" REFERENCES ").append(TableName.INGREDIENT.toString()).append("(");
		CREATE_DB.append(FOREIGN_INGREDIENT_ID_REFERENCES).append(")");
		CREATE_DB.append(");");
		
	}

	/**
	 * @see SQLiteOpenHelper
	 */
	public void onCreate(SQLiteDatabase db) {
		db.execSQL(CREATE_DB.toString());
	}

	/**
	 * @see SQLiteOpenHelper
	 */
	public void onUpgrade(SQLiteDatabase db) {
		db.execSQL("DROP TABLE "+ TABLE_RECIPESTOINGREDIENT);
		onCreate(db);
	}
	
	/**
	 * Insert a recipe to ingredient entry in the database
	 *  @param db
	 *  @param bean
	 *  @return long - 1 if the entry has been inserted, 0 otherwise 
	 */
	public long insert(SQLiteDatabase db, RecipeToIngredientBean bean) {
		//La mise à jour d'un livre dans la BDD fonctionne plus ou moins comme une insertion
		//il faut simple préciser quelle livre on doit mettre à jour grâce à l'ID

		ContentValues values = new ContentValues();
		values.put(COL_ID_RECIPES, bean.getRecipeId());
		values.put(COL_ID_INGREDIENT, bean.getIngredientId());
		return db.insert(TABLE_RECIPESTOINGREDIENT, null, values);	
	}
	
	/**
	 * remove all the entries with the specified id
	 * @return integer - the number of entries removed
	 */
	public int removeWithId(SQLiteDatabase db, int id) {
		return db.delete(TABLE_RECIPESTOINGREDIENT, COL_ID_RECIPES+" = "+id+" OR "+ COL_ID_INGREDIENT+" = "+id, null);
	}

	/**
	 * remove the relation defined with the id couple in parameter
	 * @return integer - the number of entries removed
	 */
	public int removeTheRelation(SQLiteDatabase db, int recipesId, int ingredientId) {
		return db.delete(TABLE_RECIPESTOINGREDIENT, COL_ID_RECIPES+" = "+recipesId+" AND "+ COL_ID_INGREDIENT+" = "+ingredientId, null);
	}
	
	/**
	 * Get the number of recipe to ingredient entries in the databases
	 * @param db
	 * @return the number of recipe to ingredient entries
	 */
	public int getCountInstances(SQLiteDatabase db) {
		StringBuilder query = new StringBuilder();
		query.append("SELECT count(*) ");
		query.append("from ").append(TABLE_RECIPESTOINGREDIENT);
		Cursor c = db.rawQuery(query.toString(), null);
		return cursorToCount(c);
	}
	
	private int cursorToCount(Cursor c){
		if(c.getCount()==0){
			return 0;
		}
		c.moveToFirst();
		return c.getInt(NUM_COL_COUNT);
	}

}
